Create New Measures for KPIs 12
- (1)Click to New Measure ---> (2) Rename the Measure by whatever you need by deleting “Measure” word then rename it ---> (3) Write a DAX after “=” depend on what you would like to create.
1) Create OpenEmail Measure to calculate the Emails that are
opened
- (1) Click to New Measure
- (2) Rename the Measure to OpenEmail
- (3) Write DAX OpenEmail = COUNTROWS(FILTER('MailChimp','MailChimp'[EmailOpenCount]<>"0"))
- (4) Click to
2) Create TotalEmail Measure to calculate the Total Emails that
we sent
DAX TotalEmail = COUNTROWS(FILTER('MailChimp','MailChimp'[Status]="sent"))
3) Create OpenRate Measure to calculate the Percentage of the
emails which are opened based on the Total Emails that we have sent.
DAX OpenRate = DIVIDE([OpenEmail], [Total Email])
4) Create BounceEmails Measure to calculate the Bounces Email
DAX Bounceemail = COUNTROWS(FILTER('MailChimp','MailChimp'[Status]<>"sent"))
5) Create BounceRate Measure to calculate the Percentage of the
Bounce Emails based on the Total Emails that we have sent.
DAX BounceRate = DIVIDE([BounceEmail], [Total Email])
6) Create Recipients Measure to calculate the Recipients
DAX Recipients = DISTINCTCOUNT(MailChimp[Email])
7) Create SortDate Column to sort the data from the previous to
current month
DAX Sortdate = FORMAT('MailChimp'[SentDate],"MMMM""-""YYYY")
8) Create PreMOpenRate. This is going to be used as a Target in
KPI, so that we can compare the OpenRate from the Current Month to Previous
Month.
DAX PreMOpenRate = CALCULATE([OpenRate], PREVIOUSMONTH(MailChimp[SortDate]))
9) Create PreMBounceRate for BounceRate KPI
DAX PreMBounceRate = CALCULATE( [BounceRate],PREVIOUSMONTH(MailChimp[SortDate]))
10) Create PreMRecipients for Recipients KPI
DAX PreMRecipients = CALCULATE( [Recipients], PREVIOUSMONTH(MailChimp[SortDate]))
11) Create PreMTotalEmail for Total Email KPI
DAX PreMTotalEmail = CALCULATE([Total Email],
PREVIOUSMONTH(MailChimp[SortDate]))
Format the OpenRate/BunceRate to Percentage
- (1) Double Click to OpenRate Measure ---> (2) Click to Modeling ---> (3) Click to % icon.